package com.sdkj.jdbc.utils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DBDataTool {
	private final String SELECT_SQL_FIELD = " column_name as field,";
	private final String SELECT_SQL_TYPE = " data_type as type,";
	private final String SELECT_SQL_ISDEFAULT = " column_default as isDefault,";
	private final String SELECT_SQL_MEMO = " column_comment as memo";
	
	public static void main(String[] args) {
		// 初始化数据库链接的相关信息
		DBDataTool tool = new DBDataTool();
		//查询所有数据库表信息
		List<String> tables = tool.getAllTables();
		if (tables != null) {
			for (String str : tables) {
				System.out.println(str);
			}
		}
		// 数据库表结构情况
		String DBName = "numysql";
		String TableName = "sys_user";
		List<Map<String,String>> list = tool.getTableDescBOList(DBName,TableName);
		if (list != null) {
			for (Map<String,String> map : list) {
				System.out.println(map.get("field"));
				System.out.println(map.get("type"));
			}
		}
	}

	/**
	 * 数据库表结构情况
	 * 
	 * @param dataBaseBO
	 *            数据库配置信息
	 * @return 所需查询的数据表的字段信息
	 */
	public List<Map<String,String>> getTableDescBOList(String DBName,String TableName) {
		List<Map<String,String>> list = new ArrayList<Map<String,String>>();
		String sql = "SELECT " + SELECT_SQL_FIELD + SELECT_SQL_TYPE + SELECT_SQL_ISDEFAULT + SELECT_SQL_MEMO + " FROM Information_schema.COLUMNS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? ";
		try {
			Connection conn = DBHelper.getCon();
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, DBName);
			ps.setString(2, TableName);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				Map<String,String> map = new HashMap<String,String>();
				map.put("field", rs.getString(1));
				map.put("type", rs.getString(2)=="varchar"?"String":"String");
				map.put("defaultValue", rs.getString(3) == null?"":"");
				map.put("comment", rs.getString(4));
				list.add(map);
			}
			DBHelper.close(rs, ps, conn);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * 查询所有表信息
	 * @param dataBaseBO
	 * @return
	 */
	public List<String> getAllTables(){
		List<String> list = new ArrayList<String>();
		String sql = "SHOW TABLES";
		try {
			Connection conn = DBHelper.getCon();
			PreparedStatement ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				list.add(rs.getString(1));
			}
			DBHelper.close(rs, ps, conn);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

}
